import numpy as np
import pandas as pd
import copy
import sqlite3
import os
from first_app.py_echarts import Py_Echarts
from first_app.to_sqlite3 import csv_to_sqlite
from first_app.dwon_data import Dwon_data



class Chipo(Py_Echarts):

    def __init__(self):

        self.csv_name = r"C:\Users\关亮亮\Desktop\guan_week_data\guan_data_project\first_app\chipotle.tsv"
        # self.csv_name = "chipotle.tsv"
        #
        self.chipo = pd.read_csv(self.csv_name, sep=r"\t", engine='python')

        # url = 'https://raw.githubusercontent.com/justmarkham/DAT8/master/data/chipotle.tsv'
        #
        # file_name = Dwon_data().xiazai(url)            #返回文件的名字


        # db = csv_to_sqlite(self.csv_name, sep=r"\t")          #数据库表名
        #
        # self.conn = sqlite3.connect("data.sqlite3")          #连接数据库
        #
        # sql = 'SELECT * FROM chipotle_tsv'
        #
        # self.chipo = pd.read_sql(sql, self.conn)          #把数据库里面

        self.df = copy.copy(self.chipo)

        self.df['item_price'] = self.df['item_price'].str.replace('$', '').astype('float64')

        self.df['num'] = self.df.item_price*self.df.quantity


    # 1.这段时间内餐厅的销售流水总额
    def Total_sales_of_the_restaurant(self):

        sum = self.df.num.sum()

        print(sum)

        return sum


    # 2.这段时间内销售个数最多的商品
    def The_largest_number_of_items_sold(self):

        df1 = self.df.groupby('item_name').quantity.sum().sort_values(ascending=False)

        df1 = df1.head(1)

        print(df1)

        return df1.index


    # 3.这段时间内销售金额最多的商品（5分）
    def most_money_com(self):

        df2 = self.df.groupby('item_name').num.sum().sort_values(ascending=False)

        df2 = df2.head(1)

        print(df2)
        return df2.index


    # 4.每种商品的销售个数，并做图
    def each_com_number_bar(self):

        df3 = self.df[['quantity', 'item_name']]

        df3 = df3.groupby('item_name').sum()

        attr = df3.index                         # X

        dic = df3['quantity'].to_dict()

        axis_y = [i[1] for i in dic.items()]    # Y

        bar = self.bar(attr, axis_y)

        return bar


    # 5.每种商品的销售金额，并做图（10分）
    def each_com_money_bar(self):

        df4 = self.df[['item_name', 'num']]

        df4 = df4.groupby('item_name').sum()

        attr = df4.index

        dic1 = df4['num'].to_dict()

        axis_y = [round(i[1], 2) for i in dic1.items()]

        bar = self.bar(attr, axis_y)

        return bar


    # 6.餐厅的产品的种类及对应的商品配料的价格，做出表格
    def kind_com_money_table(self):

        df5 = self.df[['item_name', 'choice_description', 'item_price']]

        df5 = df5.groupby(['item_name', 'choice_description']).sum()


        with pd.ExcelWriter('price.xlsx') as writer:

            df5.to_excel(writer)


    # 7.每种商品销售金额在流水总额中所占的比例，做圆饼图
    def each_com_all_money_ratio_pie(self):

        df6 = self.df.groupby('item_name').sum()

        attr = df6.index

        v = list(df6.num.values)

        pie = self.pie(attr, v)

        pie.render()

        return pie


    # 8.每个定单金额，并做bar图（10分）
    def each_order_id_bar(self):

        df7 = self.df[['order_id', 'num']]

        df7 = df7.groupby(['order_id']).sum()

        attr = df7.index

        v = df7.num.values

        bar = self.bar(attr, v)

        bar.render()

        return bar


    # 9.产品“Chicken Bowl”,选择最多的配料
    def chicken_bowl_toppings(self):

        df8 = self.df[self.df.item_name == 'Chicken Bowl']

        df8 = df8.groupby('choice_description').count().sort_values(by='num', ascending=False).head(1).index

        df8 = (list(df8))

        print(df8[0])
        return df8[0]


    # 10.产品“Chicken Bowl”每种配料选择的次数，及所占比例，并做圆饼图
    def chicken_bowl_number_pie(self):

        df9 = self.df[self.df.item_name == 'Chicken Bowl']

        df9 = df9.groupby('choice_description').count()

        attr = df9.index

        v = list(df9.num.values)

        pie = self.pie(attr, v)

        pie.render()

        return pie





if __name__=="__main__":
    x = Chipo()
    x.Total_sales_of_the_restaurant()
    # x.The_largest_number_of_items_sold()
    # x.each_com_number_bar()
    # x.each_com_money_bar()
    # x.kind_com_money_table()
    # x.each_com_all_money_ratio_pie()
    # x.each_order_id_bar()
    # x.chicken_bowl_toppings()
    # x.chicken_bowl_number_pie()

